package com.mallcai.bigdata.ladon.dpl.mapper.report.provider;

import com.mallcai.bigdata.ladon.dpl.entity.report.SqlRunLog;
import com.mallcai.bigdata.ladon.dpl.mapper.BaseSqlProvider;
import com.mallcai.bigdata.ladon.dpl.mapper.common.provider.DatasourceSqlProvider;
import com.mallcai.bigdata.ladon.dpl.mapper.system.provider.UserSqlProvider;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.SQL;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * @author gst
 * @date 2019-12-30 20:36
 */
public class SqlRunLogSqlProvider extends BaseSqlProvider<SqlRunLog> {

    public static final String TB_SQL_RUN_LOG = "r_sql_run_log";

    /**
     * 获取表名
     *
     * @return String
     */
    @Override
    public String getTableName() {
        return TB_SQL_RUN_LOG;
    }

    /**
     * 获取插入数据的字段列表
     *
     * @return Map
     */
    @Override
    public Map<String, String> getInsertColumns() {
        Map<String, String> map = new LinkedHashMap<>();
        map.put("ds_id", "#{dsId}");
        map.put("report_id", "#{reportId}");
        map.put("sql_id", "#{sqlId}");
        map.put("elapsed_time", "#{elapsedTime}");
        map.put("complete", "#{complete}");
        map.put("text", "#{text}");
        map.put("err_info", "#{errInfo}");
        map.put("`cached`", "#{cached}");
        return map;
    }

    /**
     * 获取更新操作的字段列表
     *
     * @return Map
     */
    @Override
    public Map<String, String> getUpdateColumns() {
        Map<String, String> map = new LinkedHashMap<>();
        map.put("dsId", "ds_id");
        map.put("reportId", "report_id");
        map.put("sqlId", "sql_id");
        map.put("elapsedTime", "elapsed_time");
        map.put("complete", "complete");
        map.put("text", "text");
        map.put("errInfo", "err_info");
        return map;
    }

    public String getByParams(Map<String, Object> map) {
        Map<String, Object> params = getParams(map);

        StringBuilder sb = new StringBuilder();
        sb.append("tb_log.archive = 0");

        if (params.containsKey("reportId") && params.get("reportId") != null) {
            sb.append(" AND tb_log.report_id = #{reportId}");
        }
        if (params.containsKey("createdBy") && params.get("createdBy") != null) {
            sb.append(" AND tb_log.created_by = #{params.createdBy}");
        }
        if (params.containsKey("elapsedTime") && params.get("elapsedTime") != null) {
            sb.append(" AND elapsed_time >= #{params.elapsedTime}");
        }
        if (params.containsKey("errInfo") && StringUtils.isNotBlank(params.get("errInfo").toString())) {
            sb.append(" and tb_log.err_info like CONCAT('%', #{params.errInfo}, '%')");
        }
        if (params.containsKey("text") && StringUtils.isNotBlank(params.get("text").toString())) {
            sb.append(" and tb_log.text like CONCAT('%', #{params.text}, '%')");
        }
        if (params.containsKey("reportCode") && StringUtils.isNotBlank(params.get("reportCode").toString())) {
            sb.append(" and tb_r.code like CONCAT('%', #{params.reportCode}, '%')");
        }
        if (params.containsKey("reportName") && StringUtils.isNotBlank(params.get("reportName").toString())) {
            sb.append(" and tb_r.name like CONCAT('%', #{params.reportName}, '%')");
        }
        return new SQL() {
            {
                SELECT("tb_r.name reportName, tb_r.code reportCode,tb_d.name dsName, tb_u.nickname AS creator, tb_log.id, tb_log.ds_id, " +
                        "tb_log.report_id, tb_log.sql_id, tb_log.elapsed_time, tb_log.complete, tb_log.created ");
                FROM(getTableName() + " AS tb_log ");
                LEFT_OUTER_JOIN(ReportSqlProvider.TB_REPORT + " tb_r ON tb_r.id = tb_log.report_id ");
                LEFT_OUTER_JOIN(DatasourceSqlProvider.TB_DATASOURCE + " tb_d ON tb_d.id = tb_log.ds_id ");
                LEFT_OUTER_JOIN(UserSqlProvider.TB_USER + " tb_u ON tb_u.id = tb_log.created_by");
                WHERE(sb.toString());
                ORDER_BY(" tb_log.created DESC ");
            }
        }.toString();
    }

    public String getRunLogStatic() {
        String dateStr = getDateStr(-1);
        return new SQL() {
            {
                SELECT("report.code as code,COUNT(1) as totalCount,COUNT(1)-SUM(r_sql.complete) AS failCount,FROM_UNIXTIME(r_sql.created,'%Y-%m-%d') as runDate," +
                        "MAX(r_sql.elapsed_time) as maxTime, MIN(r_sql.elapsed_time) as minTime ,AVG(r_sql.elapsed_time) as avgTime");
                FROM(getTableName() + " AS r_sql");
                LEFT_OUTER_JOIN(ReportSqlProvider.TB_REPORT + " as report on report.id=r_sql.report_id");
                WHERE("FROM_UNIXTIME(r_sql.created,'%Y-%m-%d') <= '" + dateStr + "'");
                GROUP_BY("runDate,r_sql.report_id");
            }
        }.toString();
    }

    public String getYesterdayRunLogStatic() {
        String dateStr = getDateStr(-1);
        return new SQL() {
            {
                SELECT("report.code as code,COUNT(1) as totalCount,COUNT(1)-SUM(r_sql.complete) AS failCount,FROM_UNIXTIME(r_sql.created,'%Y-%m-%d') as runDate," +
                        "MAX(r_sql.elapsed_time) as maxTime, MIN(r_sql.elapsed_time) as minTime ,AVG(r_sql.elapsed_time) as avgTime");
                FROM(getTableName() + " AS r_sql");
                LEFT_OUTER_JOIN(ReportSqlProvider.TB_REPORT + " as report on report.id=r_sql.report_id");
                WHERE("FROM_UNIXTIME(r_sql.created,'%Y-%m-%d')= '" + dateStr + "'");
                GROUP_BY("r_sql.report_id");
            }
        }.toString();
    }

    public String getTodayVisitSize() {
        String dateStr = getDateStr(0);
        return new SQL() {
            {
                SELECT(" COUNT(*)");
                FROM(getTableName());
                WHERE("created > UNIX_TIMESTAMP('" + dateStr + "')");
            }
        }.toString();
    }

    public String deleteLogOneWeekAgo() {
        String dateStr = getDateStr(-7);
        return new SQL() {
            {
                DELETE_FROM(getTableName());
                WHERE("created < UNIX_TIMESTAMP('" + dateStr + "')");
            }
        }.toString();
    }

    private String getDateStr(Integer added) {
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DAY_OF_YEAR, added);
        Date date = rightNow.getTime();
        return simpleDateFormat.format(date);
    }
}
